package com.itheima02.dao;

import com.itheima02.domain.Student;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoDemo implements StudentDao{
    @Override
    public List<Student> findAll() {
        Connection con = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        ArrayList<Student> list = null;
        try {
            // 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 获取连接
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db4", "root", "123");
            // 定义sql语句
            String sql = "select * from student";
            
            // 获取执行对象执行sql语句，并接收结果
            stat = con.prepareStatement(sql);
            rs = stat.executeQuery();

            // 创建集合
            list = new ArrayList<>();
            // 处理结果
            while (rs.next()){
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                // 封装学生对象
                Student stu = new Student(sid,name,age,birthday);
                // 添加到集合中
                list.add(stu);
            }

            // 释放资源
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public Student findByid(Integer id) {
        Connection con = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        Student stu = null;
        try {
            // 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 获取连接
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db4", "root", "123");
            // 定义sql语句
            String sql = "select * from student where sid=?";
            // 获取执行对象执行sql语句，并接收结果
            stat = con.prepareStatement(sql);
            stat.setInt(1,id);
            rs = stat.executeQuery();
            // 处理结果
            while (rs.next()){
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                stu = new Student(sid,name,age,birthday);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return stu;
    }

    @Override
    public int insert(Student stu) {
        Connection con =null;
        PreparedStatement stat =null;
        int i =0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db4","root","123");
            java.util.Date date = stu.getBirthday();
            String format = new SimpleDateFormat("yyyy-MM-dd").format(date);
            String sql = "insert into student values ('"+stu.getSid()+"','"+stu.getName()+"','"+stu.getAge()+"','"+format+"')";
            stat = con.prepareStatement(sql);
            i = stat.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return i;
    }

    @Override
    public int update(Student stu) {
        Connection con = null;
        PreparedStatement stat = null;
        int i = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db4","root","123");
            String format = new SimpleDateFormat("yyyy-MM-dd").format(stu.getBirthday());
            String sql = "update student set name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+format+"' where sid=?";
            stat = con.prepareStatement(sql);
            stat.setInt(1,stu.getSid());
            i = stat.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return i;
    }

    @Override
    public int delete(Integer sid) {
        Connection con = null;
        PreparedStatement stat = null;
        int i = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db4", "root", "123");
            String sql = "delete from student where sid=?";
            stat = con.prepareStatement(sql);
            stat.setInt(1,sid);
            i = stat.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return i;
    }
}
